package com.etc.util;



import org.apache.commons.beanutils.BeanUtils;

import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据访问通用类
 */
public final class DBUtil {

	private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3307/zmall?serverTimezone=Asia/Shanghai";
	private static final String USER = "root";
	private static final String PASSWORD = "root";

	private DBUtil() {
	}

	/**
	 * 获取连接对象Connection
	 *
	 * @return java.sql.Connection
	 */
	public static Connection getConn() {
		Connection conn = null;
		try {
			Class.forName(DRIVER);
			// 2创建connection
			// 得到连接对象
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return conn;
	}

	/**
	 * 通用的增加，删除，修改的代码
	 *
	 * @param sql    要执行的sql语句
	 * @param params 占位符的值，可以用数组来传递或者直接赋值
	 * @return int 受影响的行
	 */
	public static int exUpdate(String sql, Object... params) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		int result = 0;
		try {
			conn = getConn();

			// 3创建一个PreparedStatement =>增加 删除 修改 完全一致，除了sql语句不同
			pstmt = conn.prepareStatement(sql);
			// 调用设置参数的方法
			setPstmt(pstmt, params);
			// 5 执行sql
			result = pstmt.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 释放资源
			closeAll(pstmt, conn, null);
		}

		return result;

	}

	/**
	 * 设置参数,补齐占位符
	 *
	 * @param pstmt  PreparedStatement
	 * @param params 占位符的数据值列表(数组)
	 */
	private static void setPstmt(PreparedStatement pstmt, Object... params) {
		// 补齐占位符
		if (params != null) {
			for (int i = 0; i < params.length; i++) {
				try {
					pstmt.setObject(i + 1, params[i]);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}

		System.out.println(pstmt);
	}

	/**
	 * 释放资源你的方法
	 *
	 * @param pstmt PreparedStatement 对象
	 * @param conn  Connection 连接对象
	 * @param rs    ResultSet 结果集对象
	 */
	public static void closeAll(PreparedStatement pstmt, Connection conn, ResultSet rs) {
		// 6 释放资源
		try {
			if (pstmt != null) {
				pstmt.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 *通用的查询的方法
	 * @param sql
	 * @param cls
	 * @param params
	 * @return
	 */
	public static List exQuery(String sql, Class cls, Object... params) {

		List list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet result = null;
		try {
			conn = getConn();

			// 3创建一个PreparedStatement =>增加 删除 修改 完全一致，除了sql语句不同
			pstmt = conn.prepareStatement(sql);
			// 调用设置参数的方法
			setPstmt(pstmt, params);
			// 5 执行sql
			result = pstmt.executeQuery();

			while (result.next()) {
				list.add(convert(result, cls));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 释放资源
			closeAll(pstmt, conn, result);
		}

		return list;
	}

	/**
	 * 查询的方法
	 *
	 * @param rs  结果集
	 * @param cls Class对象
	 * @return 对象
	 */
	public static Object convert(ResultSet rs, Class cls) {
		Object obj = null;
		try {
			obj = cls.newInstance();
			ResultSetMetaData rsmd = rs.getMetaData();

			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				String name = rsmd.getColumnLabel(i);
				Object value = rs.getObject(i);
				//处理查询的数据 和 实体类 对象 属性值转换,如果数据库列类型date则Result获取的类型为java.sql.Date
				//如果数据库列类型datetime则Result获取的类型为localdatetime
//				System.out.println(value.getClass().getName());
//				if (value.getClass().getName().equals("java.sql.Date")){
//					Date temp = (Date) value;
//					value = temp.toLocalDate();
//				}
				BeanUtils.setProperty(obj, name, value);
			}

		} catch (SQLException | InstantiationException | IllegalAccessException | InvocationTargetException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return obj;

	}

	/**
	 * 记录数查询
	 *
	 * @param countSQL    查询sql语句 count()
	 * @param cls    Object
	 * @param params
	 * @return long记录数有几条
	 */
	public static long getTotalCount(String countSQL, Class cls, Object... params) {
		//// select count(*) from
		// (select * from news where (newstitle like ? or newscontent like ?) order by
		//// pubdate desc) as temp

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		long result = 0;
		try {
			// 获取连接对象
			conn = getConn();
			// 创建pstmt对象
			pstmt = conn.prepareStatement(countSQL);
			// parmas当成数组来处理
			setPstmt(pstmt, params);
			// 执行查询的方法
			rs = pstmt.executeQuery();
			// 判断Class -->查询单个值
			if (cls.getName().equals("java.lang.Object")) {
				// 单个值
				if (rs.next()) {
					result = rs.getLong(1);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 释放资源
			closeAll(pstmt, conn, rs);
		}
		return result;

	}

	/**
	 * @func 分页查询
	 * @param sql
	 * @param cls
	 * @param pageNo
	 * @param pageSize
	 * @param params
	 * @return
	 */
	public static PageData exQueryByPage(String sql, Class cls, int pageNo, int pageSize, Object... params) {
		// sql ?
		// select * from news where (newstitle like '%测%' or newscontent like '%测%')
		// order by pubdate desc

		// 1 查询记录数
		// select count(*) from
		// (select * from news where (newstitle like '%测%' or newscontent like '%测%')
		// order by pubdate desc) as temp

		String countSQL = "select count(1) from (" + sql + ") as temp";
		long result = getTotalCount(countSQL, Object.class, params);
		// 2 limit
		// select * from news where (newstitle like '%测%' or newscontent like '%测%')
		// order by pubdate desc
		// limit 0,5
		int start = (pageNo - 1) * pageSize;
		sql = sql + " limit " + start + "," + pageSize;

		// list就是存放查询到到结果集
		List list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			// 获取连接对象
			conn = getConn();
			// 创建pstmt对象
			pstmt = conn.prepareStatement(sql);
			setPstmt(pstmt, params);
			// 执行查询的方法
			rs = pstmt.executeQuery();
			// 查询多个列 -> 遍历
			while (rs.next()) {
				// 得到一行数据就调用convert方法将列的数据填充到一个对象中，然后返回
				// Object obj ->Type News
				Object obj = convert(rs, cls);
				// 将对象添加到list集合中
				list.add(obj);
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  finally {
			// 释放资源
			closeAll(pstmt, conn, rs);
		}
		// 构造一个PageData对象
		PageData pd = new PageData(pageNo, pageSize, result, list);
		// 返回结果
		return pd;
	}

}
